package cn.escco.ocr.dto.bo;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import cn.escco.ocr.dto.po.JybBizHdcpOdds;
import cn.escco.ocr.dto.vo.PlatPlayHdcpOddsVo;
import cn.escco.ocr.dto.vo.PlatPlayHdcpVo;
import cn.escco.ocr.dto.vo.PlatPlayVo;
import cn.escco.ocr.dto.vo.PlatTypeLeaTeamVsVo;
import cn.escco.ocr.dto.vo.PlatTypeLeaVo;
import cn.escco.ocr.dto.vo.PlatTypeVo;
import cn.escco.ocr.dto.vo.PlatVo;

@Mapper
public interface JybBizHdcpOddsDao extends BaseMapper<JybBizHdcpOdds> {

    @Select("SELECT MAX(bho.time_stamp) max_time_stamp\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + "   AND bho.type_code = #{typeCode}\n"
            + "   AND bho.lea_code = #{leaCode}\n"
            + "   AND bho.team_code_left = #{teamCodeLeft}\n"
            + "   AND bho.team_code_right = #{teamCodeRight}\n"
            + "   AND bho.play_code = #{playCode}\n")
    String getMaxTimeStamp(@Param("baseDate") String baseDate, @Param("platCode") String platCode, @Param("typeCode") String typeCode, @Param("leaCode") String leaCode, @Param("teamCodeLeft") String teamCodeLeft, @Param("teamCodeRight") String teamCodeRight, @Param("playCode") String playCode);

    @Select("SELECT bho.time_stamp, bho.play_hdcp, bho.odds_left, bho.odds_right\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + "   AND bho.type_code = #{typeCode}\n"
            + "   AND bho.lea_code = #{leaCode}\n"
            + "   AND bho.team_code_left = #{teamCodeLeft}\n"
            + "   AND bho.team_code_right = #{teamCodeRight}\n"
            + "   AND bho.play_code = #{playCode}\n"
            + "   AND bho.time_stamp > #{timeStampStart}\n"
            + "   AND bho.time_stamp <= #{timeStampEnd}\n"
            + " GROUP BY bho.time_stamp, bho.play_hdcp\n"
            + " ORDER BY bho.time_stamp, bho.play_hdcp")
    List<PlatPlayHdcpOddsVo> getPlatPlayHdcpOddss(@Param("baseDate") String baseDate, @Param("platCode") String platCode, @Param("typeCode") String typeCode, @Param("leaCode") String leaCode, @Param("teamCodeLeft") String teamCodeLeft, @Param("teamCodeRight") String teamCodeRight, @Param("playCode") String playCode, @Param("timeStampStart") String timeStampStart, @Param("timeStampEnd") String timeStampEnd);


    @Select("SELECT bho.play_hdcp\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + "   AND bho.type_code = #{typeCode}\n"
            + "   AND bho.lea_code = #{leaCode}\n"
            + "   AND bho.team_code_left = #{teamCodeLeft}\n"
            + "   AND bho.team_code_right = #{teamCodeRight}\n"
            + "   AND bho.play_code = #{playCode}\n"
            + "   AND bho.time_stamp <= #{timeStampEnd}\n"
            + " GROUP BY bho.play_hdcp\n"
            + " ORDER BY bho.play_hdcp")
    List<PlatPlayHdcpVo> getPlatPlayHdcps(@Param("baseDate") String baseDate, @Param("platCode") String platCode, @Param("typeCode") String typeCode, @Param("leaCode") String leaCode, @Param("teamCodeLeft") String teamCodeLeft, @Param("teamCodeRight") String teamCodeRight, @Param("playCode") String playCode, @Param("timeStampEnd") String timeStampEnd);

    @Select("SELECT bho.play_code, mpp.play_name\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + "  LEFT JOIN jyb_core.jyb_mst_plat_play mpp\n"
            + "    ON mpp.plat_code = bho.plat_code\n"
            + "   AND mpp.play_code = bho.play_code\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + "   AND bho.type_code = #{typeCode}\n"
            + "   AND bho.lea_code = #{leaCode}\n"
            + "   AND bho.team_code_left = #{teamCodeLeft}\n"
            + "   AND bho.team_code_right = #{teamCodeRight}\n"
            + " GROUP BY bho.play_code\n"
            + " ORDER BY mpp.play_flag DESC, mpp.play_sort")
    List<PlatPlayVo> getPlatPlays(@Param("baseDate") String baseDate, @Param("platCode") String platCode, @Param("typeCode") String typeCode, @Param("leaCode") String leaCode, @Param("teamCodeLeft") String teamCodeLeft, @Param("teamCodeRight") String teamCodeRight);

    @Select("SELECT bho.team_code_left, mptltl.team_name team_name_left, bho.team_code_right, mptltr.team_name team_name_right\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + "  LEFT JOIN jyb_core.jyb_mst_plat_type_lea_team mptltl\n"
            + "    ON mptltl.plat_code = bho.plat_code\n"
            + "   AND mptltl.type_code = bho.type_code\n"
            + "   AND mptltl.lea_code = bho.lea_code\n"
            + "   AND mptltl.team_code = bho.team_code_left\n"
            + "  LEFT JOIN jyb_core.jyb_mst_plat_type_lea_team mptltr\n"
            + "    ON mptltr.plat_code = bho.plat_code\n"
            + "   AND mptltr.type_code = bho.type_code\n"
            + "   AND mptltr.lea_code = bho.lea_code\n"
            + "   AND mptltr.team_code = bho.team_code_right\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + "   AND bho.type_code = #{typeCode}\n"
            + "   AND bho.lea_code = #{leaCode}\n"
            + " GROUP BY bho.team_code_left, bho.team_code_right\n"
            + " ORDER BY mptltl.team_flag DESC, mptltl.team_sort, mptltr.team_flag DESC, mptltr.team_sort")
    List<PlatTypeLeaTeamVsVo> getPlatTypeLeaTeamVss(@Param("baseDate") String baseDate, @Param("platCode") String platCode, @Param("typeCode") String typeCode, @Param("leaCode") String leaCode);

    @Select("SELECT bho.lea_code, mptl.lea_name\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + "  LEFT JOIN jyb_core.jyb_mst_plat_type_lea mptl\n"
            + "    ON mptl.plat_code = bho.plat_code\n"
            + "   AND mptl.type_code = bho.type_code\n"
            + "   AND mptl.lea_code = bho.lea_code\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + "   AND bho.type_code = #{typeCode}\n"
            + " GROUP BY bho.lea_code\n"
            + " ORDER BY mptl.lea_flag DESC, mptl.lea_sort")
    List<PlatTypeLeaVo> getPlatTypeLeas(@Param("baseDate") String baseDate, @Param("platCode") String platCode, @Param("typeCode") String typeCode);

    @Select("SELECT bho.type_code, mpt.type_name\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + "  LEFT JOIN jyb_core.jyb_mst_plat_type mpt\n"
            + "    ON mpt.plat_code = bho.plat_code\n"
            + "   AND mpt.type_code = bho.type_code\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + "   AND bho.plat_code = #{platCode}\n"
            + " GROUP BY bho.type_code\n"
            + " ORDER BY mpt.type_flag DESC, mpt.type_sort")
    List<PlatTypeVo> getPlatTypes(@Param("baseDate") String baseDate, @Param("platCode") String platCode);

    @Select("SELECT bho.plat_code, mp.plat_name\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + "  LEFT JOIN jyb_core.jyb_mst_plat mp\n"
            + "    ON mp.plat_code = bho.plat_code\n"
            + " WHERE bho.hdcp_odds_date = #{baseDate}\n"
            + " GROUP BY bho.plat_code\n"
            + " ORDER BY mp.plat_flag DESC, mp.plat_sort")
    List<PlatVo> getPlats(@Param("baseDate") String baseDate);

    @Select("SELECT bho.hdcp_odds_date\n"
            + "  FROM jyb_core.jyb_biz_hdcp_odds bho\n"
            + " GROUP BY bho.hdcp_odds_date\n"
            + " ORDER BY bho.hdcp_odds_date DESC")
    List<String> getBaseDates();
}
